using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;


namespace EM.Repository.utility
{
    public class FillCombo
    {
        public void Currency(DropDownList _DropDownList, bool ShowEmptyItem)
        {
            DbHelper srv = new DbHelper();
            SqlCommand cmd = new SqlCommand();

            string sSQL = "";
            sSQL += " SELECT CURRENCYID, CURRENCYCODE ";
            sSQL += " FROM TBLCURRENCY ";
            cmd.CommandText = sSQL;
            DataTable Dt = srv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["CURRENCYID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["CURRENCYCODE"].ToString();


                if (ShowEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr["CURRENCYID"] = "0";
                    Dr["CURRENCYCODE"] = " ";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
            }
        }

        public void Bank(DropDownList _DropDownList, bool showEmptyItem, int selectedItemValue, bool onlyActiveFlg)
        {
            DbHelper srv = new DbHelper();
            SqlCommand cmd = new SqlCommand();

            string sSQL = "";
            sSQL += " SELECT  BANKID, BANKNAME ";
            sSQL += " FROM TBLBANK ";
            sSQL += " WHERE 1= 1 ";
            if (onlyActiveFlg)
                sSQL += " AND ACTIVEFLG = 'Y' ";
            sSQL += " ORDER BY BANKNAME ";

            cmd.CommandText = sSQL;
            DataTable Dt = srv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["BANKID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["BANKNAME"].ToString();


                if (showEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr["BANKID"] = "0";
                    Dr["BANKNAME"] = " ";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
                _DropDownList.SelectedValue = selectedItemValue.ToString();
            }
        }

        public void ExpenseType(DropDownList _DropDownList, int SelectedItemValue, bool ShowEmptyItem, int workGroupId)
        {
            string sSQL = "";
            sSQL = @"SELECT EXPENSETYPEID, EXPENSETYPENAME
			        FROM TBLEXPENSETYPE
			        WHERE 1 = 1 ";

            sSQL += " AND WORKGROUPID = " + workGroupId;

            sSQL += " ORDER BY EXPENSETYPENAME ASC";

            DbHelper Dsrv = new DbHelper();
            SqlCommand cmd = new SqlCommand(sSQL);

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["EXPENSETYPEID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["EXPENSETYPENAME"].ToString();


                if (ShowEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr[0] = "0";
                    Dr[1] = "-";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
                _DropDownList.SelectedValue = SelectedItemValue.ToString();
            }
        }

        public void IncomeType(DropDownList _DropDownList, int SelectedItemValue, bool ShowEmptyItem, int workGroupId)
        {
            string sSQL = "";
            sSQL = @"SELECT INCOMETYPEID, INCOMETYPENAME
			        FROM TBLINCOMETYPE
			        WHERE 1 = 1";

            sSQL += " AND WORKGROUPID = " + workGroupId;

            DbHelper Dsrv = new DbHelper();
            SqlCommand cmd = new SqlCommand(sSQL);

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["INCOMETYPEID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["INCOMETYPENAME"].ToString();


                if (ShowEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr[0] = "0";
                    Dr[1] = "-";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
                _DropDownList.SelectedValue = SelectedItemValue.ToString();
            }
        }

        public void BankAccount(DropDownList _DropDownList, int SelectedItemValue, bool ShowEmptyItem, int bankId, int workGroupId)
        {
            DbHelper Dsrv = new DbHelper();

            string sSQL = "";
            SqlCommand cmd = new SqlCommand(sSQL);
            sSQL = @"SELECT BANKACCOUNTID, BANKACCOUNTNAME + ' - ' + IBAN AS BANKACCOUNTNAME
			        FROM TBLBANKACCOUNT
			        WHERE 1 = 1";
            if (bankId > 0)
            {
                sSQL += " AND BANKID = @BANKID ";
                cmd.Parameters.AddWithValue("@BANKID", bankId);
            }

            sSQL += " AND WORKGROUPID = " + workGroupId;


            cmd.CommandText = sSQL;
            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["BANKACCOUNTID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["BANKACCOUNTNAME"].ToString();


                if (ShowEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr[0] = "0";
                    Dr[1] = "-";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
                _DropDownList.SelectedValue = SelectedItemValue.ToString();
            }
        }

        public void CreditCard(DropDownList _DropDownList, int SelectedItemValue, bool ShowEmptyItem, int bankId, int workGroupId)
        {
            DbHelper Dsrv = new DbHelper();

            string sSQL = "";
            SqlCommand cmd = new SqlCommand(sSQL);
            sSQL = @"SELECT CREDITCARDID, CREDITCARDNAME
			        FROM TBLCREDITCARD
			        WHERE 1 = 1";
            if (bankId > 0)
            {
                sSQL += " AND BANKID = @BANKID ";
                cmd.Parameters.AddWithValue("@BANKID", bankId);
            }
            sSQL += " AND WORKGROUPID = " + workGroupId;


            cmd.CommandText = sSQL;
            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["CREDITCARDID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["CREDITCARDNAME"].ToString();


                if (ShowEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr[0] = "0";
                    Dr[1] = "-";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
                _DropDownList.SelectedValue = SelectedItemValue.ToString();
            }
        }

        private void bindList(ListControl listControl, DataTable dtSource, string selectedValue, bool showEmptyItem)
        {
            listControl.Items.Clear();
            if (dtSource.Rows.Count > -1)
            {
                listControl.DataSource = dtSource;
                listControl.DataValueField = dtSource.Columns[0].ToString();
                listControl.DataTextField = dtSource.Columns[1].ToString();
                listControl.DataBind();
                if (showEmptyItem)
                    listControl.Items.Insert(0, new ListItem("-", ""));
                listControl.SelectedValue = selectedValue;
            }
        }

        public void Content(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string LangCode)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";
            sSQL += " SELECT CONTENTID, CONTENTNAME";
            sSQL += " FROM TBLCONTENT_L ";
            sSQL += " WHERE LANGCODE = @LANGCODE ";
            sSQL += " ORDER BY CONTENTNAME ";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;
            cmd.Parameters.Add("@LANGCODE", SqlDbType.NVarChar).Value = LangCode;
            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void Role(ListControl listControl, string SelectedValue, bool ShowEmptyItem, int workgroupId)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";
            sSQL += " SELECT ROLEID, ROLENAME FROM TBLROLE";
            sSQL += " WHERE STATUS = 'A' AND WORKGROUPID = " + workgroupId;
            sSQL += " ORDER BY ROLENAME ";

            DataTable Dt = Dsrv.GetDataTable(sSQL, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void City(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string COUNTRY)
        {

            DbHelper Dsrv = new DbHelper();
            string sSQL = "";
            sSQL = "SELECT CITYID, CITYNAME ";
            sSQL += " FROM TBLCITY ";
            sSQL += " WHERE 1 = 1";
            if (COUNTRY != "")
            {
                sSQL += " AND COUNTRY = '" + COUNTRY + "'";
            }
            else
            {
                sSQL += " AND COUNTRY = '-1'";
            }
            sSQL += " ORDER BY CITYNAME";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void State(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string COUNTRY, int CITYID)
        {

            DbHelper Dsrv = new DbHelper();
            string sSQL = "SELECT STATEID, STATEDESC FROM TBLCITYSTATE WHERE COUNTRY = '" + COUNTRY + "' AND CITYID = " + CITYID.ToString() + " ORDER BY STATEDESC";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void LookupDepartment_type(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string LangCode)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";
            sSQL += " SELECT LVL.LOOKUPVALUE, LVL.LOOKUPNAME ";
            sSQL += " FROM TBLLOOKUPVALUE_L LVL, TBLLOOKUPVALUE LV ";
            sSQL += " WHERE LVL.LOOKUPTYPE = 'SYS_LOOKUP_TYPE' ";
            sSQL += " AND LVL.LANGCODE = @LANGCODE ";
            sSQL += " AND LVL.LOOKUPTYPE = LV.LOOKUPTYPE ";
            sSQL += " AND LVL.LOOKUPVALUE = LV.LOOKUPVALUE ";
            sSQL += " AND LVL.LOOKUPVALUE <> 'Sys' ";
            sSQL += " AND GETDATE() BETWEEN LV.STARTDATE AND ISNULL(LV.ENDDATE,'01/01/2099')";
            sSQL += " ORDER BY LV.SEQUENCE ";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;
            cmd.Parameters.Add("@LANGCODE", SqlDbType.NVarChar).Value = LangCode;

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void Lookuptype_type(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string LangCode)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";
            sSQL += " SELECT LVL.LOOKUPVALUE, LVL.LOOKUPNAME";
            sSQL += " FROM TBLLOOKUPVALUE_L LVL, TBLLOOKUPVALUE LV";
            sSQL += " WHERE LVL.LOOKUPTYPE = 'SYS_LOOKUP_TYPE_TYPE'";
            sSQL += " AND LVL.LANGCODE = @LANGCODE";
            sSQL += " AND LVL.LOOKUPTYPE = LV.LOOKUPTYPE";
            sSQL += " AND LVL.LOOKUPVALUE = LV.LOOKUPVALUE";
            sSQL += " AND LVL.LOOKUPVALUE <> 'S'";
            sSQL += " AND GETDATE() BETWEEN LV.STARTDATE AND ISNULL(LV.ENDDATE,'01/01/2099')";
            sSQL += " ORDER BY LV.SEQUENCE";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;
            cmd.Parameters.Add("@LANGCODE", SqlDbType.NVarChar).Value = LangCode;

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void LookUpValue(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string LookUpType, string LangCode)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";
            sSQL += " SELECT LV.LOOKUPVALUE, LV.LOOKUPNAME, L.SEQUENCE";
            sSQL += " FROM TBLLOOKUPVALUE_L LV , TBLLOOKUPVALUE L";
            sSQL += " WHERE LV.LOOKUPTYPE = @LOOKUPTYPE ";
            sSQL += " AND L.LOOKUPTYPE = LV.LOOKUPTYPE ";
            sSQL += " AND L.LOOKUPVALUE = LV.LOOKUPVALUE ";
            sSQL += " AND LANGCODE=@LANGCODE";
            sSQL += " AND GETDATE() BETWEEN L.STARTDATE AND ISNULL(L.ENDDATE,'01/01/2099')";
            sSQL += " ORDER BY L.SEQUENCE, LV.LOOKUPNAME";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;
            cmd.Parameters.Add("@LOOKUPTYPE", SqlDbType.NVarChar).Value = LookUpType;
            cmd.Parameters.Add("@LANGCODE", SqlDbType.NVarChar).Value = LangCode;

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void LookUpValue(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string LookUpType, string LangCode, string LookUpContext, bool hasContext)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";

            sSQL += " SELECT LV.LOOKUPVALUE, LV.LOOKUPNAME, L.SEQUENCE";
            sSQL += " FROM TBLLOOKUPVALUE_L LV , TBLLOOKUPVALUE L";
            sSQL += " WHERE LV.LOOKUPTYPE = @LOOKUPTYPE";
            sSQL += " AND L.LOOKUPTYPE = LV.LOOKUPTYPE";
            sSQL += " AND L.LOOKUPVALUE = LV.LOOKUPVALUE";
            sSQL += " AND LANGCODE = @LANGCODE";
            if (hasContext) sSQL += " AND L.CONTEXT = @CONTEXT";
            else sSQL += " AND L.CONTEXT <> @CONTEXT";
            sSQL += " AND GETDATE() BETWEEN L.STARTDATE AND ISNULL(L.ENDDATE,'01/01/2099')";
            sSQL += " ORDER BY L.SEQUENCE, LV.LOOKUPNAME";
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;
            cmd.Parameters.Add("@LOOKUPTYPE", SqlDbType.NVarChar).Value = LookUpType;
            cmd.Parameters.Add("@LANGCODE", SqlDbType.NVarChar).Value = HttpContext.Current.Session["LANGCODE"].ToString();
            cmd.Parameters.Add("@CONTEXT", SqlDbType.NVarChar).Value = LookUpContext;

            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void User(ListControl listControl, string SelectedValue, bool ShowEmptyItem, int workGroupId)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";

            sSQL += @" SELECT USERID, USERDESC
                         FROM TBLUSER WHERE USERCODE <> 'system' AND WORKGROUPID = " + workGroupId;
            sSQL += "  ORDER BY USERDESC";

            DataTable Dt = Dsrv.GetDataTable(sSQL, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void Currency(ListControl listControl, string SelectedValue, bool ShowEmptyItem)
        {
            StringBuilder sSQL = new StringBuilder(" SELECT CURRENCYID, CURRENCYCODE FROM TBLCURRENCY ");
            DataTable dt = new DbHelper().GetDataTable(sSQL.ToString(), "");
            this.bindList(listControl, dt, SelectedValue, ShowEmptyItem);
        }

        public void Month(ListControl listControl, string SelectedValue, bool ShowEmptyItem)
        {
            DataTable Dt = new DataTable();
            Dt.Columns.Add("DESC", typeof(string));
            Dt.Columns.Add("VALUE", typeof(string));
            for (int i = 1; i <= 12; i++)
            {
                DataRow Dr = Dt.NewRow();
                Dr[0] = i;
                Dt.Rows.Add(Dr[0], Dr[0]);
            }
            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void Year(ListControl listControl, string SelectedValue, bool ShowEmptyItem)
        {
            DataTable Dt = new DataTable();
            Dt.Columns.Add("DESC", typeof(string));
            Dt.Columns.Add("VALUE", typeof(string));
            for (int i = 2006; i <= 2020; i++)
            {
                DataRow Dr = Dt.NewRow();
                Dr[0] = i;
                Dt.Rows.Add(Dr[0], Dr[0]);
            }
            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void Year(ListControl listControl, string SelectedValue, bool ShowEmptyItem, int StartYear, int EndYear)
        {
            DataTable Dt = new DataTable();
            Dt.Columns.Add("DESC", typeof(string));
            Dt.Columns.Add("VALUE", typeof(string));

            for (int i = StartYear; i <= EndYear; i++)
            {
                DataRow Dr = Dt.NewRow();
                Dr[0] = i;
                Dt.Rows.Add(Dr[0], Dr[0]);
            }
            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void LookUpValue_GetDesc(ListControl listControl, string SelectedValue, bool ShowEmptyItem, string LookUpType, string LangCode)
        {
            DbHelper Dsrv = new DbHelper();
            StringBuilder sSQL = new StringBuilder();
            SqlCommand Cmd = new SqlCommand();

            sSQL.Append(" SELECT LV.LOOKUPVALUE, LV.LOOKUPNAME, LV.LOOKUPDESC ");
            sSQL.Append(" FROM TBLLOOKUPVALUE_L LV , TBLLOOKUPVALUE L");
            sSQL.Append(" WHERE LV.LOOKUPTYPE = @LOOKUPTYPE ");
            sSQL.Append(" AND L.LOOKUPTYPE = LV.LOOKUPTYPE ");
            sSQL.Append(" AND L.LOOKUPVALUE = LV.LOOKUPVALUE ");
            sSQL.Append(" AND LANGCODE=@LANGCODE");
            sSQL.Append(" AND GETDATE() BETWEEN L.STARTDATE AND ISNULL(L.ENDDATE,'01/01/2099')");
            sSQL.Append(" ORDER BY L.SEQUENCE, LV.LOOKUPNAME");

            Cmd.CommandText = sSQL.ToString();
            Cmd.Parameters.Add("@LOOKUPTYPE", SqlDbType.NVarChar).Value = LookUpType;
            Cmd.Parameters.Add("@LANGCODE", SqlDbType.NVarChar).Value = LangCode;

            DataTable Dt = Dsrv.GetDataTable(Cmd, "TBLCBO");

            listControl.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                foreach (DataRow oRow in Dt.Rows)
                {
                    ListItem item = new ListItem(oRow["LOOKUPNAME"].ToString(), oRow["LOOKUPVALUE"].ToString());
                    item.Attributes["Desc"] = oRow["LOOKUPDESC"].ToString();
                    listControl.Items.Add(item);
                }

                if (ShowEmptyItem)
                    listControl.Items.Insert(0, new ListItem("-", ""));
                listControl.SelectedValue = SelectedValue;
            }
        }

        public void BankBranch(ListControl listControl, string SelectedValue, bool ShowEmptyItem)
        {
            DbHelper Dsrv = new DbHelper();
            string sSQL = "";
            sSQL += " SELECT BANKBRANCHID, BRANCHNAME FROM TBLBANKBRANCH";
            sSQL += " ORDER BY BRANCHNAME ";

            DataTable Dt = Dsrv.GetDataTable(sSQL, "TBLCBO");

            this.bindList(listControl, Dt, SelectedValue, ShowEmptyItem);
        }

        public void BankBranch(DropDownList _DropDownList, string SelectedValue, bool ShowEmptyItem, int bankId)
        {
            DbHelper Dsrv = new DbHelper();

            string sSQL = "";
            SqlCommand cmd = new SqlCommand(sSQL);
            sSQL = @"SELECT BANKBRANCHID, BRANCHNAME FROM TBLBANKBRANCH
			        WHERE 1 = 1";
            if (bankId > 0)
            {
                sSQL += " AND BANKID = @BANKID ";
                cmd.Parameters.AddWithValue("@BANKID", bankId);
            }

            sSQL += " ORDER BY BRANCHNAME ASC ";

            cmd.CommandText = sSQL;
            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["BANKBRANCHID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["BRANCHNAME"].ToString();


                if (ShowEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr[0] = "0";
                    Dr[1] = "";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
                _DropDownList.SelectedValue = SelectedValue.ToString();
            }
        }

        public void Workgroup(DropDownList _DropDownList, int SelectedItemValue, bool ShowEmptyItem)
        {
            DbHelper Dsrv = new DbHelper();

            string sSQL = "";
            SqlCommand cmd = new SqlCommand(sSQL);
            sSQL = @"SELECT WORKGROUPID, WORKGROUP
			        FROM TBLWORKGROUP
			        WHERE 1 = 1 ";


            cmd.CommandText = sSQL;
            DataTable Dt = Dsrv.GetDataTable(cmd, "TBLCBO");

            _DropDownList.Items.Clear();

            if (Dt.Rows.Count > -1)
            {
                _DropDownList.DataSource = Dt;
                _DropDownList.DataValueField = Dt.Columns["WORKGROUPID"].ToString();
                _DropDownList.DataTextField = Dt.Columns["WORKGROUP"].ToString();


                if (ShowEmptyItem)
                {
                    DataRow Dr = Dt.NewRow();
                    Dr[0] = "0";
                    Dr[1] = "-";
                    Dt.Rows.InsertAt(Dr, 0);
                }
                _DropDownList.DataBind();
                _DropDownList.SelectedValue = SelectedItemValue.ToString();
            }
        }

    }
}
